﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data.Linq.Mapping;
using System.Data;
using System.Data.Common;

namespace CP.DBService
{
    public class SqlSegmentBuilder<T>
    {
        string selectTemplate = "Select {0} from {1}";
        string whereTemplate = " {0} = {1}{2}{1} ";
        string insertTemplate = "Insert into {0}({1}) Values({2})";
        string updateTemplate = "Update {0} Set {1} Where {2}";
        string deleteTemplate = "Delete from {0} Where {1}";

        string select = "";
        IList<string> where = new List<string>();

        public string SelectCommandText
        {
            get
            {
                string cmd = "";
                cmd = select;

                if (where.Count > 0)
                {
                    cmd += " Where ";
                    foreach (var wh in where)
                    {
                        cmd += wh + " And ";
                    }
                    cmd = cmd.Substring(0, cmd.Length - 5);
                }
                return cmd;
            }
        }

        public SqlSegmentBuilder<T> BuildSelect()
        {
            select = Format(selectTemplate, "*", typeof(T).Name);
            return this;
        }

        public SqlSegmentBuilder<T> BuildWherePK<PKType>(PKType value)
        {
            T entity = Activator.CreateInstance<T>();
            var properties = entity.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
            where.Clear();
            string keyString = value.ToString();
            string[] keys = keyString.Split(',');
            //int index = 0;

            foreach (var property in properties)
            {
                var attributes = property.GetCustomAttributes(typeof(ColumnAttribute), true);
                if (attributes != null && attributes.Length > 0)
                {
                    var attr = attributes[0] as ColumnAttribute;
                    if (attr != null && attr.IsPrimaryKey)
                    {
                        int position = Convert.ToInt16(string.IsNullOrEmpty(attr.Name) ? "0" : attr.Name);
                        if (attr.IsPrimaryKey && position < keys.Count() && position > 0)
                            where.Add(Format(whereTemplate, property.Name, (property.PropertyType == typeof(int) ? "" : "'"), keys[position]));

                        //if (keys[index] == "") keys[index] = " ";
                        //if (attr.IsPrimaryKey && index<keys.Count())
                        //{
                        //    where.Add(Format(whereTemplate, property.Name, (property.PropertyType == typeof(int) ? "" : "'"), keys[index]));
                        //    index++;
                        //}
                    }

                }

            }

            return this;
        }

        public void BuildInsertCommand(T entity, DbCommand command)
        {
            //SqlCommand command = new SqlCommand();

            //(1)构建Insert语句
            string tableName = typeof(T).Name;
            string fields = "";
            string parametersName = "";
            var properties = entity.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (var property in properties)
            {
                bool flag = true;
                var attributes = property.GetCustomAttributes(typeof(ColumnAttribute), true);
                if (attributes != null && attributes.Length > 0)
                {
                    var attr = attributes[0] as ColumnAttribute;
                    if (attr.IsDbGenerated == true)
                    {
                        //flag = false;
                    }
                }

                if (flag)
                {
                    fields += "" + property.Name + ",";
                    parametersName += ":" + property.Name + ",";
                }
            }
            if (fields.EndsWith(","))
                fields = fields.Substring(0, fields.Length - 1);
            if (parametersName.EndsWith(","))
                parametersName = parametersName.Substring(0, parametersName.Length - 1);

            command.CommandText = Format(insertTemplate, tableName, fields, parametersName);

            //(2)构建参数
            properties = entity.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (var property in properties)
            {
                bool flag = true;
                var attributes = property.GetCustomAttributes(typeof(ColumnAttribute), true);
                if (attributes != null && attributes.Length > 0)
                {
                    var attr = attributes[0] as ColumnAttribute;
                    if (attr.IsDbGenerated == true)
                    {
                        //flag = false;
                    }
                }
                if (flag)
                {
                    object filed_value = property.GetValue(entity, null);
                    if (property.PropertyType == typeof(string) && string.IsNullOrEmpty((string)filed_value)) filed_value = " ";
                    //if (filed_value == null) filed_value = DBNull.Value;
                    //SqlParameter parameter = new SqlParameter(":" + property.Name, filed_value);
                    DbParameter parameter = command.CreateParameter();
                    parameter.ParameterName = ":" + property.Name;

                    parameter.Value = filed_value;

                    //数据验证逻辑
                    command.Parameters.Add(parameter);
                }
            }

            //return command;
        }

        public void BuildUpdateCommand(T entity, DbCommand command)
        {
            var properties = entity.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);

            //(1)构建Insert语句
            string tableName = typeof(T).Name;
            string fields = "";
            foreach (var property in properties)
            {
                bool flag = true;
                var attributes = property.GetCustomAttributes(typeof(ColumnAttribute), true);
                if (attributes != null && attributes.Length > 0)
                {
                    var attr = attributes[0] as ColumnAttribute;
                    if (attr.IsPrimaryKey == true)
                    {
                        flag = false;
                    }
                }

                if (flag)
                {
                    fields += "" + property.Name + " = :" + property.Name + ",";
                }
            }
            if (fields.EndsWith(","))
                fields = fields.Substring(0, fields.Length - 1);



            //(2)构建参数
            foreach (var property in properties)
            {
                bool flag = true;
                var attributes = property.GetCustomAttributes(typeof(ColumnAttribute), true);
                if (attributes != null && attributes.Length > 0)
                {
                    var attr = attributes[0] as ColumnAttribute;
                    if (attr.IsPrimaryKey == true)
                    {
                        flag = false;
                    }
                }
                if (flag)
                {
                    object filed_value = property.GetValue(entity, null);
                    //if (filed_value == null) filed_value = DBNull.Value;
                    if (property.PropertyType == typeof(string) && string.IsNullOrEmpty((string)filed_value)) filed_value = " ";
                    DbParameter parameter = command.CreateParameter();
                    parameter.ParameterName = ":" + property.Name;
                    parameter.Value = filed_value;

                    //数据验证逻辑
                    command.Parameters.Add(parameter);
                }
            }

            //(3)构建条件字段
            string where = "";
            foreach (var property in properties)
            {
                var attributes = property.GetCustomAttributes(typeof(ColumnAttribute), true);
                if (attributes != null && attributes.Length > 0)
                {
                    var attr = attributes[0] as ColumnAttribute;
                    if (attr.IsPrimaryKey)
                    {
                        where += " and " + property.Name + " = :" + property.Name;
                        //SqlParameter parameter = new SqlParameter("@" + property.Name, property.GetValue(entity, null));
                        object filed_value = property.GetValue(entity, null);
                        if (property.PropertyType == typeof(string) && string.IsNullOrEmpty((string)filed_value)) filed_value = " ";
                        DbParameter parameter = command.CreateParameter();
                        parameter.ParameterName = ":" + property.Name;
                        parameter.Value = filed_value;
                        command.Parameters.Add(parameter);
                    }
                }
            }
            if (where.StartsWith(" and "))
                where = where.Substring(" and ".Length);
            command.CommandText = Format(updateTemplate, tableName, fields, where);
        }

        public void BuildDeleteCommand(T entity, DbCommand command)
        {
            //SqlCommand command = new SqlCommand();
            var properties = entity.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);

            string tableName = typeof(T).Name;

            //(2)构建条件字段
            string where = "";
            foreach (var property in properties)
            {
                var attributes = property.GetCustomAttributes(typeof(ColumnAttribute), true);
                if (attributes != null && attributes.Length > 0)
                {
                    var attr = attributes[0] as ColumnAttribute;
                    if (attr.IsPrimaryKey)
                    {
                        where += " and " + property.Name + " = :" + property.Name;
                        object filed_value = property.GetValue(entity, null);
                        //if (filed_value.GetType() == typeof(string) && filed_value.ToString() == "") filed_value = " ";
                        if (property.PropertyType == typeof(string) && string.IsNullOrEmpty((string)filed_value)) filed_value = " ";
                        DbParameter parameter = command.CreateParameter();
                        parameter.ParameterName = ":" + property.Name;
                        parameter.Value = filed_value;
                        command.Parameters.Add(parameter);
                    }
                }
            }
            if (where.StartsWith(" and "))
                where = where.Substring(" and ".Length);
            command.CommandText = Format(deleteTemplate, tableName, where);
            //return command;
        }

        public void BuildeDeleteCommandByPK<PKType>(PKType value, DbCommand command)
        {

            string tableName = typeof(T).Name;

            T entity = Activator.CreateInstance<T>();
            var properties = entity.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
            string keyString = value.ToString();
            string[] keys = keyString.Split(',');
            int index = 0;
            string where = "";

            foreach (var property in properties)
            {
                var attributes = property.GetCustomAttributes(typeof(ColumnAttribute), true);
                if (attributes != null && attributes.Length > 0)
                {
                    var attr = attributes[0] as ColumnAttribute;
                    if (attr.IsPrimaryKey)
                    {
                        if (keys[index] == "") keys[index] = " ";
                        where += " and " + (Format(whereTemplate, property.Name, (property.PropertyType == typeof(int) ? "" : "'"), keys[index]));
                        index++;
                    }

                }

            }
            if (where.StartsWith(" and "))
                where = where.Substring(" and ".Length);
            command.CommandText = Format(deleteTemplate, tableName, where);

        }

        public void Clear()
        {
            select = "";
            where.Clear();
        }

        public override string ToString()
        {
            return base.ToString();
        }

        private string Format(string input, params object[] args)
        {
            return string.Format(input, args);
        }
    }
}
